import json

import pandas as pd
from pypinyin import pinyin, Style

import mysql_util as sql_util


# 字典表
def ch_to_pinyin(str):
    pinyin_list = pinyin(str, style=Style.FIRST_LETTER)  # 设置拼音风格
    rs = ''
    for k in pinyin_list:
        rs += ''.join(k)
    return rs


"""查询用户是否分配配权限"""
if __name__ == '__main__':
    file_path = 'C:/Users/yangjianzhang/Desktop/dict.xlsx'
    # 使用read_excel函数读取Excel文件并将其存储为DataFrame对象
    df = pd.read_excel(file_path)

    # 打印读取到的数据
    # print(df)
    print(type(df))
    mobiles = []
    flags = []
    dict_id = '127'
    for index, row in df.iterrows():
        cj_name = row['场景名称']
        dw_name = row['摄像头点位']
        # 查询字典的id
        sql = "SELECT * FROM sys_dict_item di where di.dict_id = '111' and item_text = '{}'".format(dw_name)
        rs = sql_util.select_by_sql(sql)
        rs = json.loads(rs)
        if len(rs) > 0:
            location_code = rs[0]['item_value']
            business_code = ch_to_pinyin(cj_name)
            # 插入字典表
            del_sql = "delete from sys_dict_item where dict_id = {} and item_value = '{}'".format(dict_id,
                                                                                                  business_code)
            sql = "INSERT INTO sys_dict_item( `dict_id`, `item_text`, `item_value`, `description`, `sort_order`, `status`" \
                  ", `create_by`, `create_time`, `update_by`, `update_time`) VALUES ( '{}', '{}', '{}', NULL, {}, 1, 'py'" \
                  ", now(), NULL, NULL)".format(dict_id, cj_name, ch_to_pinyin(business_code), index)
            sql_util.execute(del_sql)
            sql_util.execute(sql)
            # 插入关联表
            sql = "INSERT INTO `pc_business_location_link`( `business_code`, `location_code`, `create_by`, `update_by`" \
                  ", `create_time`, `update_time`) VALUES ('{}', '{}', NULL, NULL, now(),now());".format(
                business_code, location_code)
            sql_util.execute(sql)
